#!/bin/bash

# Ford UK 车型数据完整性分析脚本

RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
CYAN='\033[0;36m'
NC='\033[0m' # No Color

echo "🔍 Ford UK 车型数据完整性分析"
echo "================================================"
echo "分析时间: $(date)"
echo ""

# Ford UK 官方车型列表 (基于2024-2025年型号)
declare -A FORD_UK_MODELS
FORD_UK_MODELS=(
    # 轿车和掀背车
    ["Fiesta"]="Active Style ST ST-Line Titanium Vignale"
    ["Focus"]="Style ST-Line Titanium Vignale ST RS"
    ["Mondeo"]="Titanium Vignale Hybrid"
    
    # SUV和Crossover
    ["Puma"]="Titanium ST-Line ST-Line-X ST"
    ["Kuga"]="Titanium ST-Line Vignale PHEV"
    ["Explorer"]="Limited Platinum ST"
    ["Bronco"]="Base Big-Bend Outer-Banks Badlands Wildtrak Raptor"
    ["Mustang-Mach-E"]="Select Premium GT Route-1"
    
    # 商用车/MPV
    ["Galaxy"]="Zetec Titanium Titanium-X"
    ["S-MAX"]="Titanium Vignale ST-Line"
    ["B-MAX"]="Style Zetec Titanium"
    ["Tourneo-Custom"]="Zetec Titanium Sport"
    ["Transit"]="Leader Trend Limited"
    ["Transit-Custom"]="Leader Trend Limited Sport"
    ["E-Transit"]="Leader Trend Limited"
    ["E-Transit-Custom"]="Leader Trend Limited"
    
    # 性能车
    ["Mustang"]="EcoBoost GT Mach-1 Shelby-GT350 Shelby-GT500"
    
    # 小型SUV
    ["EcoSport"]="Zetec Titanium ST-Line"
    
    # 商用皮卡
    ["Ranger"]="Regular XL XLT Lariat Wildtrak Raptor"
    
    # 豪华SUV
    ["Edge"]="Zetec Titanium Vignale Sport"
)

echo "📊 官方车型对比分析"
echo "----------------------------------------"

# 从数据库获取我们的车型数据
OUR_MODELS=$(mysql -u root -p123 -D dcp-cloud -se "SELECT DISTINCT model FROM make_info_uk ORDER BY model;")

echo "我们抓取的车型数据:"
echo "$OUR_MODELS"
echo ""

echo "逐一对比分析:"
echo "----------------------------------------"

# 统计
TOTAL_OFFICIAL=0
TOTAL_OUR=0
MISSING_MODELS=0
EXTRA_MODELS=0

# 计算官方车型总数
for model in "${!FORD_UK_MODELS[@]}"; do
    TOTAL_OFFICIAL=$((TOTAL_OFFICIAL + 1))
done

# 计算我们的车型总数
TOTAL_OUR=$(echo "$OUR_MODELS" | wc -l)

echo "官方车型分析:"
for model in "${!FORD_UK_MODELS[@]}"; do
    expected_variants="${FORD_UK_MODELS[$model]}"
    
    # 处理模型名称匹配（处理连字符差异）
    model_search=$(echo "$model" | sed 's/-/ /g')
    our_variants=$(mysql -u root -p123 -D dcp-cloud -se "SELECT GROUP_CONCAT(variant ORDER BY variant SEPARATOR ', ') FROM make_info_uk WHERE model LIKE '%$model_search%' OR model = '$model';")
    
    if [ -n "$our_variants" ] && [ "$our_variants" != "NULL" ]; then
        echo -e "${GREEN}✅ $model${NC}"
        echo "   官方变体: $expected_variants"
        echo "   我们的变体: $our_variants"
    else
        echo -e "${RED}❌ $model${NC} - 缺失"
        echo "   官方变体: $expected_variants"
        MISSING_MODELS=$((MISSING_MODELS + 1))
    fi
    echo ""
done

echo ""
echo "我们独有的车型（可能是官方未列出或已停产）:"
echo "----------------------------------------"
while IFS= read -r our_model; do
    found=false
    for official_model in "${!FORD_UK_MODELS[@]}"; do
        # 处理模型名称匹配
        official_clean=$(echo "$official_model" | sed 's/-/ /g')
        if [[ "$our_model" =~ $official_clean ]] || [[ "$official_clean" =~ $our_model ]]; then
            found=true
            break
        fi
    done
    
    if [ "$found" = false ]; then
        echo -e "${YELLOW}⚠️ $our_model${NC} - 我们独有"
        variants=$(mysql -u root -p123 -D dcp-cloud -se "SELECT GROUP_CONCAT(variant ORDER BY variant SEPARATOR ', ') FROM make_info_uk WHERE model = '$our_model';")
        echo "   变体: $variants"
        EXTRA_MODELS=$((EXTRA_MODELS + 1))
    fi
done <<< "$OUR_MODELS"

echo ""
echo "🏷️ 分类分析"
echo "----------------------------------------"
echo "按Ford官方分类检查:"

echo ""
echo "🚗 乘用车分类:"
passenger_cars=("Fiesta" "Focus" "Mondeo")
for car in "${passenger_cars[@]}"; do
    count=$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(*) FROM make_info_uk WHERE model LIKE '%$car%';")
    if [ "$count" -gt 0 ]; then
        echo -e "  ${GREEN}✅ $car ($count 个变体)${NC}"
    else
        echo -e "  ${RED}❌ $car (缺失)${NC}"
    fi
done

echo ""
echo "🚙 SUV/Crossover分类:"
suvs=("Puma" "Kuga" "Explorer" "Bronco" "Mustang Mach-E" "EcoSport" "Edge")
for suv in "${suvs[@]}"; do
    count=$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(*) FROM make_info_uk WHERE model LIKE '%$suv%';")
    if [ "$count" -gt 0 ]; then
        echo -e "  ${GREEN}✅ $suv ($count 个变体)${NC}"
    else
        echo -e "  ${RED}❌ $suv (缺失)${NC}"
    fi
done

echo ""
echo "🚐 商用车/MPV分类:"
commercial=("Galaxy" "S-MAX" "B-MAX" "Tourneo Custom" "Transit" "Transit Custom" "E-Transit" "E-Transit Custom")
for vehicle in "${commercial[@]}"; do
    count=$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(*) FROM make_info_uk WHERE model LIKE '%$vehicle%';")
    if [ "$count" -gt 0 ]; then
        echo -e "  ${GREEN}✅ $vehicle ($count 个变体)${NC}"
    else
        echo -e "  ${RED}❌ $vehicle (缺失)${NC}"
    fi
done

echo ""
echo "🏎️ 性能车分类:"
performance=("Mustang" "Focus RS" "Focus ST" "Fiesta ST" "Puma ST")
for perf in "${performance[@]}"; do
    count=$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(*) FROM make_info_uk WHERE model LIKE '%$perf%' OR variant LIKE '%$perf%';")
    if [ "$count" -gt 0 ]; then
        echo -e "  ${GREEN}✅ $perf ($count 个变体)${NC}"
    else
        echo -e "  ${RED}❌ $perf (缺失)${NC}"
    fi
done

echo ""
echo "🚚 皮卡分类:"
pickups=("Ranger")
for pickup in "${pickups[@]}"; do
    count=$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(*) FROM make_info_uk WHERE model LIKE '%$pickup%';")
    if [ "$count" -gt 0 ]; then
        echo -e "  ${GREEN}✅ $pickup ($count 个变体)${NC}"
    else
        echo -e "  ${RED}❌ $pickup (缺失)${NC}"
    fi
done

echo ""
echo "📊 完整性统计报告"
echo "================================================"
echo -e "官方车型总数: ${BLUE}$TOTAL_OFFICIAL${NC}"
echo -e "我们抓取总数: ${BLUE}$TOTAL_OUR${NC}"
echo -e "缺失车型数量: ${RED}$MISSING_MODELS${NC}"
echo -e "额外车型数量: ${YELLOW}$EXTRA_MODELS${NC}"

coverage_percentage=$(( (TOTAL_OUR - EXTRA_MODELS + TOTAL_OFFICIAL - MISSING_MODELS) * 100 / TOTAL_OFFICIAL / 2 ))
echo -e "覆盖率估算: ${CYAN}$coverage_percentage%${NC}"

echo ""
echo "🔍 数据质量分析"
echo "----------------------------------------"

# 检查价格完整性
price_coverage=$(mysql -u root -p123 -D dcp-cloud -se "SELECT ROUND(COUNT(CASE WHEN price_from IS NOT NULL AND price_from > 0 THEN 1 END) * 100.0 / COUNT(*), 1) FROM make_info_uk;")
echo -e "价格信息完整率: ${CYAN}$price_coverage%${NC}"

# 检查详情链接完整性
url_coverage=$(mysql -u root -p123 -D dcp-cloud -se "SELECT ROUND(COUNT(CASE WHEN detail_url IS NOT NULL AND detail_url != '' THEN 1 END) * 100.0 / COUNT(*), 1) FROM make_info_uk;")
echo -e "详情链接完整率: ${CYAN}$url_coverage%${NC}"

# 检查规格信息
specs_coverage=$(mysql -u root -p123 -D dcp-cloud -se "SELECT ROUND(COUNT(CASE WHEN specifications IS NOT NULL AND specifications != '' THEN 1 END) * 100.0 / COUNT(*), 1) FROM make_info_uk;")
echo -e "规格信息完整率: ${CYAN}$specs_coverage%${NC}"

echo ""
echo "💡 建议改进措施"
echo "----------------------------------------"
if [ "$MISSING_MODELS" -gt 0 ]; then
    echo "1. 补充缺失的 $MISSING_MODELS 个官方车型"
fi

if [ "$price_coverage" -lt 100 ]; then
    echo "2. 完善价格信息（当前 $price_coverage%）"
fi

if [ "$specs_coverage" -lt 100 ]; then
    echo "3. 补充规格信息（当前 $specs_coverage%）"
fi

echo "4. 考虑添加更多变体和配置选项"
echo "5. 定期更新以反映官网最新变化"

echo ""
echo "🎯 探索建议"
echo "----------------------------------------"
echo "建议探索的Ford UK页面:"
echo "• https://www.ford.co.uk/cars - 主要乘用车"
echo "• https://www.ford.co.uk/vans-and-pickups - 商用车"
echo "• https://www.ford.co.uk/performance - 性能车系列"
echo "• https://www.ford.co.uk/electric-vehicles - 电动车系列"
echo "• 各车型的具体配置页面"
